﻿using log4net;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading;
using System.Windows.Forms;
using ZKDevice;
using log4net.Layout;
using System.Timers;

namespace ZKServer
{

    public partial class Server : Form
    {
        enum AccessControlState
        {
	        待下载 = 1,
	        己下载 = 2,
	        待删除 = 3,
	        删除失败 = 4,
	        下载失败 = 5
        };

        private const string pattern = @"^((2[0-4]\d|25[0-5]|[01]?\d\d?)\.){3}(2[0-4]\d|25[0-5]|[01]?\d\d?)$";
        private readonly ILog log = LogManager.GetLogger(typeof(Server));
        private bool run = false;
        private readonly string configFilePath = "config.txt";

        private string ip;
        private string username;
        private string password;

        private DatabaseHelper db = new DatabaseHelper();
        private Dictionary<string, ZKDeviceAPI> zkDeviceCache = new Dictionary<string, ZKDeviceAPI>();
        private Dictionary<string, string> zkDeviceNameCache = new Dictionary<string, string>();

        private delegate void UpdateDataGridView(DataSet ds);

        public Server()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 启动定时数据库检索与F2下载指纹服务
        /// </summary>
        private void button_start_Click(object sender, EventArgs e)
        {
            this.ip = textBox_ip.Text.Trim();
            this.username = textBox_username.Text.Trim();
            this.password = textBox_password.Text.Trim();

            if(!Regex.Match(ip, pattern).Success)
            {
                MessageBox.Show("服务器ip格式不正确");
                return;
            }

            if(username.Length == 0 || password.Length == 0)
            {
                MessageBox.Show("用户名或密码不可为空");
                return;
            }

            SaveConfig(ip,username,password);

            run = true;
            Thread thread = new Thread(Start);
            thread.Start();

            button_start.Visible = false;
            button_exit.Visible = true;
            
            log.Info("指纹服务器下载任务己启动");
        }

        private void Start()
        {
            Thread.Sleep(5000);
            while (run)
            {
                try
                {
                    RefreshTask();
                    Thread.Sleep(3000);
                }
                catch (Exception)
                {

                }
                finally
                {
                    
                }
            }
        }

        private void RefreshTask()
        {
            log.Info("检查权限列表状态");
            int taskSize = 0;
            IAsyncResult iAsyncResult = null;
            try
            {
                string sql_task_query = "select top 20 acr.id as '流水号',d.Name as '设备名称',l.address as '设备地址',pc.SerialNumber as '卡片编号',pc.physicalId as '卡片内码',cu.name as '用户姓名', (select count(1) from CardUserFingerTemplate cuft where cuft.cardUser_id = cu.id and cuft.tmp is not null ) as '指纹数量',(select count(1) from CardUserFaceTemplate cuf where cuf.cardUser = cu.id and cuf.tmp is not null ) as '人脸数量',(case AccessControlState when 1 then '待下载' when 3 then '待删除' else '' end) as '状态' from AccessControlRecord acr left join Device d on acr.device = d.id left join Link l on l.id = d.Link left join PhysicalCard pc on pc.id = acr.card_id left join CardUser cu on cu.id = pc.cardUser where d.Type = 'FaceFingerOneDevice' and acr.card_id is not null and (acr.accessControlState = 1 or acr.accessControlState = 3) order by acr.device asc,acr.createTime asc";
                DataSet ds = db.query(ip, username, password, sql_task_query);
                iAsyncResult = dataGridView_task.BeginInvoke(new UpdateDataGridView(UpdateDataGridViewMethod), ds);
                DataRowCollection rows = ds.Tables[0].Rows;

                taskSize = rows.Count;

                if (taskSize == 0)
                {
                    RestartFailRecord(db);
                }

                if (taskSize == 0)
                {
                    ReadAllDeviceLog();
                }

                foreach (DataRow dr in rows)
                {
                    UserInfo userInfo = ConvertUserInfo(dr);
                    if (!userInfo.IsIdentifierValidRight())
                    {
                        continue;
                    }

                    ZKDeviceAPI zkDevice = GetZkDevice(userInfo.address);

                    if (!zkDevice.IsConnected())
                    {
                        UpdateRecordStateWhenDisConnected(userInfo);
                        continue;
                    }

                    if (userInfo.state == "待删除")
                    {
                        ProcessDeleteRecord(userInfo, zkDevice);
                        continue;
                    }

                    if (userInfo.state == "待下载")
                    {
                        ProcessDownloadRecord(userInfo, zkDevice);
                        continue;
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error("执行权限任务失败", ex);
            }
            finally
            {
                if (iAsyncResult != null)
                {
                    dataGridView_task.EndInvoke(iAsyncResult);
                }
                if (taskSize == 0)
                {
                    Thread.Sleep(5000);
                }
            }
        }

        /// <summary>
        /// 关闭服务，关闭系统
        /// </summary>
        private void button_exit_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("你确定要退出服务吗？", "确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
            {
                run = false;
                this.Close();
                this.Dispose();
                Application.Exit();
            }
        }

        /// <summary>
        /// 阻止单击右上角窗口关闭按钮退出，应该隐藏到通知栏
        /// </summary>
        private void Server_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (run)
            {
                e.Cancel = true;//不关闭程序
                MessageBox.Show("正在运行指纹下载服务，无法关闭");
            }
        }

        /// <summary>
        /// 界面打开，初始化数据绑定
        /// </summary>
        private void Server_Load(object sender, EventArgs e)
        {
            log.Info("指纹服务器己打开");
            ReadConfig();

            textBox_ip.Text = ip;
            textBox_username.Text = username;
            textBox_password.Text = password;

            var logPattern = "%d{yyyy-MM-dd HH:mm:ss} --%-5p-- %m%n";
            var textBox_logAppender = new TextBoxBaseAppender()
            {
                TextBox = this.textBox_log,
                Layout = new PatternLayout(logPattern)
            };
            log4net.Config.BasicConfigurator.Configure(textBox_logAppender);
        }

        /// <summary>
        /// 读取服务器配置
        /// </summary>
        private void ReadConfig()
        {
            if (!File.Exists(configFilePath))
            {
                return;
            }

            using(StreamReader sr = new StreamReader(configFilePath))
            {
                ip = sr.ReadLine();
                username = sr.ReadLine();
                password = sr.ReadLine();
            }
        }

        /// <summary>
        /// 保存服务器配置
        /// </summary>
        private void SaveConfig(string ip, string username, string password)
        {
            try
            {
                if (File.Exists(configFilePath))
                {
                    File.Delete(configFilePath);
                }

                using (StreamWriter m_SW = new StreamWriter(configFilePath))
                {
                    m_SW.WriteLine(ip);
                    m_SW.WriteLine(username);
                    m_SW.WriteLine(password);
                }

                log.InfoFormat("写入配置成功！{0} {1} {2}", ip, username, password);
            }
            catch (Exception ex)
            {
                log.Error("保存配置异常", ex);
            }
        }

        /// <summary>
        /// 读取所有设备打卡记录
        /// </summary>
        private void ReadAllDeviceLog()
        {
            string sql_deviceList = "select address,linkName from Link where protocol = 'Attendance'";
            DataSet ds = db.query(ip, username, password, sql_deviceList);
            DataRowCollection rows = ds.Tables[0].Rows;
            foreach (DataRow dr in rows)
            {
                string address = dr.Field<string>("address");
                ZKDeviceAPI zkDevice = GetZkDevice(address.Split(':')[0]);
                List<AttendanceLog> logList = zkDevice.ReadAttendanceLog();
                foreach(AttendanceLog att in logList)
                {
                    List<SqlParameter> sqlParameterList = new List<SqlParameter>();
                    
                    string sql_query_user = "select top 1 pc.PhysicalId,pc.SerialNumber,cu.identifier,cu.name,cu.groupCodeJoinStr,cu.groupCodeNameJoinStr from PhysicalCard pc left join CardUser CU on pc.cardUser = CU.id where pc.SerialNumber = '"+att.identifier+"'";
                    DataSet ds_user = db.query(ip, username, password, sql_query_user);
                    DataRowCollection user_rows = ds_user.Tables[0].Rows;
                    if(user_rows.Count > 0)
                    {
                        sqlParameterList.Add(new SqlParameter("@cardid", user_rows[0].Field<string>("PhysicalId")));
                        sqlParameterList.Add(new SqlParameter("@cardSerialNumber", user_rows[0].Field<string>("SerialNumber")));
                        sqlParameterList.Add(new SqlParameter("@userIdentifier", user_rows[0].Field<string>("identifier")));
                        sqlParameterList.Add(new SqlParameter("@userName", user_rows[0].Field<string>("name")));
                        sqlParameterList.Add(new SqlParameter("@userGroupCodeJoinStr", user_rows[0].Field<string>("groupCodeJoinStr")));
                        sqlParameterList.Add(new SqlParameter("@userGroupCodeNameJoinStr", user_rows[0].Field<string>("groupCodeNameJoinStr")));
                    }
                    else
                    {
                        continue;
                    }


                    string sql_query_device = "select top 1  d.Identifier,d.Name,d.groupCodeJoinStr,d.groupCodeNameJoinStr,d.Type from Device d left join Link L on d.Link = L.id where l.address = '"+ address + "'";
                    DataSet ds_device = db.query(ip, username, password, sql_query_device);
                    DataRowCollection device_rows = ds_device.Tables[0].Rows;
                    if (device_rows.Count > 0)
                    {
                        sqlParameterList.Add(new SqlParameter("@deviceGroupCodeJoinStr", device_rows[0].Field<string>("groupCodeJoinStr")));
                        sqlParameterList.Add(new SqlParameter("@deviceGroupCodeNameJoinStr", device_rows[0].Field<string>("groupCodeNameJoinStr")));
                        sqlParameterList.Add(new SqlParameter("@deviceIdentifier", device_rows[0].Field<string>("Identifier")));
                        sqlParameterList.Add(new SqlParameter("@deviceName", device_rows[0].Field<string>("Name")));
                        sqlParameterList.Add(new SqlParameter("@deviceTypeEnum", device_rows[0].Field<string>("Type")));
                    }

                    //@preventBreak,@doorSwitchPressed,@emergencyInput,@doorOpen
                    sqlParameterList.Add(new SqlParameter("@preventBreak", false));
                    sqlParameterList.Add(new SqlParameter("@doorSwitch", false));
                    sqlParameterList.Add(new SqlParameter("@emergency", false));
                    sqlParameterList.Add(new SqlParameter("@doorOpen", false));

                    sqlParameterList.Add(new SqlParameter("@eventType", 23));
                    sqlParameterList.Add(new SqlParameter("@eventtime", att.logTime.ToString("yyyy-MM-dd HH:mm:ss")));
                    sqlParameterList.Add(new SqlParameter("@timestamp", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));

                    string sql_insert = "insert into CardUsage (type, cardid, cardSerialNumber, deviceGroupCodeJoinStr, deviceGroupCodeNameJoinStr, deviceIdentifier, deviceName, deviceTypeEnum, eventType, eventtime, timestamp, userGroupCodeJoinStr, userGroupCodeNameJoinStr, userIdentifier, userName,preventBreak,doorSwitch,emergency,doorOpen) values ('normal',@cardid, @cardSerialNumber, @deviceGroupCodeJoinStr, @deviceGroupCodeNameJoinStr, @deviceIdentifier, @deviceName, @deviceTypeEnum, @eventType, @eventtime, @timestamp, @userGroupCodeJoinStr, @userGroupCodeNameJoinStr, @userIdentifier, @userName,@preventBreak,@doorSwitch,@emergency,@doorOpen)";
                    db.execute(ip, username, password, sql_insert, sqlParameterList.ToArray());
                    log.InfoFormat("保存刷卡记录成功,编号{0} 时间{1} 设备{2}", att.identifier, att.logTime.ToString("yyyy-MM-dd HH:mm:ss"), address);
                }
            }
        }

        /// <summary>
        /// 当设备连接失败时，更新任务状态
        /// </summary>
        private void UpdateRecordStateWhenDisConnected(UserInfo userInfo)
        {
            if (userInfo.state == "待下载")
            {
                UpdateRecord(AccessControlState.待下载, AccessControlState.下载失败, userInfo.id);
            }
            else if (userInfo.state == "待删除")
            {
                UpdateRecord(AccessControlState.待删除, AccessControlState.删除失败, userInfo.id);
            }
        }

        /// <summary>
        /// 处理待下载的任务
        /// </summary>
        private void ProcessDownloadRecord(UserInfo userInfo, ZKDeviceAPI zkDevice)
        {
            if (userInfo.fingerSize > 0)
            {
                byte[][] fingerArr = new byte[10][];

                string sql_finger = "select fid,tmp from CardUserFingerTemplate cuft left join CardUser cu on cuft.cardUser_id = cu.id left join PhysicalCard pc on pc.cardUser = cu.id where cuft.tmp is not null and pc.SerialNumber = '" + userInfo.identifier + "'";
                DataSet ds_finger = db.query(ip, username, password, sql_finger);
                DataRowCollection drc = ds_finger.Tables[0].Rows;
                foreach (DataRow dr_finger in drc)
                {
                    int fid = Int32.Parse(dr_finger.Field<string>("fid"));
                    byte[] fingers = dr_finger.Field<byte[]>("tmp");
                    fingerArr[fid] = fingers;
                }
                userInfo.fingerList = fingerArr.ToList();
            }

            if (userInfo.faceSize > 0)
            {
                string sql_face = "select top 1 tmp from CardUserFaceTemplate cuft left join CardUser cu on cuft.cardUser = cu.id left join PhysicalCard pc on pc.cardUser = cu.id where cuft.tmp is not null and pc.SerialNumber = '" + userInfo.identifier + "'";
                DataSet ds_face = db.query(ip, username, password, sql_face);
                DataRowCollection drc = ds_face.Tables[0].Rows;
                foreach (DataRow dr_face in drc)
                {
                    byte[] faces = dr_face.Field<byte[]>("tmp");
                    userInfo.face = faces;
                }
            }

            if (zkDevice.ExistUserInfo(userInfo.identifier))
            {
                zkDevice.DeleteUserInfo(userInfo.identifier);
            }
            
            bool download = zkDevice.DownloadUserInfo(userInfo);
            bool download_check = zkDevice.ValidUserInfo(userInfo.identifier, userInfo.fingerSize);
            if (download && download_check)
            {
                UpdateRecord(AccessControlState.待下载, AccessControlState.己下载, userInfo.id);
            }
            else
            {
                UpdateRecord(AccessControlState.待下载, AccessControlState.下载失败, userInfo.id);
            }
        }

        /// <summary>
        /// 处理待删除的任务
        /// </summary>
        private void ProcessDeleteRecord(UserInfo userInfo, ZKDeviceAPI zkDevice)
        {
            log.InfoFormat("开始删除用户【{0}-{1}】权限", userInfo.identifier, userInfo.name);
            if (!zkDevice.ExistUserInfo(userInfo.identifier))
            {
                DeleteSuccessRecord(AccessControlState.待删除, userInfo.id);
                return;
            }

            if (zkDevice.DeleteUserInfo(userInfo.identifier))
            {
                DeleteSuccessRecord(AccessControlState.待删除, userInfo.id);
            }
            else
            {
                UpdateRecord(AccessControlState.待删除, AccessControlState.删除失败, userInfo.id);
            }
        }

        /// <summary>
        /// 转换当前数据行到UserInfo
        /// </summary>
        private UserInfo ConvertUserInfo(DataRow dr)
        {
            UserInfo userInfo = new UserInfo();
            userInfo.id = dr.Field<Int64>("流水号");
            userInfo.fingerSize = dr.Field<Int32>("指纹数量");
            userInfo.faceSize = dr.Field<Int32>("人脸数量");
            userInfo.name = dr.Field<string>("用户姓名");
            userInfo.address = dr.Field<string>("设备地址").Split(':')[0];
            userInfo.deviceName = dr.Field<string>("设备名称");
            userInfo.cardId = dr.Field<string>("卡片编号");
            userInfo.state = dr.Field<string>("状态");
            userInfo.fingerList = new List<byte[]>(10);

            int identifierResult = 0;
            string identifierStr = dr.Field<string>("卡片编号");
            if (!Int32.TryParse(identifierStr,out identifierResult))
            {
                log.InfoFormat("用户编号只能为5位纯数字 {0}", identifierStr);
            }else
            {
                userInfo.identifier = identifierResult;
            }
            
            return userInfo;
        }

        /// <summary>
        /// 重置失败的下载任务
        /// </summary>
        private void RestartFailRecord(DatabaseHelper db)
        {
            string sql_updateAllFail = "update AccessControlRecord set accessControlState = 1 where accessControlState = 5 and device in (select id from Device where Type = 'FaceFingerOneDevice')";
            db.execute(ip, username, password, sql_updateAllFail,null);
            string sql_updateAllFail2 = "update AccessControlRecord set accessControlState = 3 where accessControlState = 4 and device in (select id from Device where Type = 'FaceFingerOneDevice')";
            db.execute(ip, username, password, sql_updateAllFail2,null);
        }

        /// <summary>
        /// 获取缓存中的中控设备
        /// </summary>
        private ZKDeviceAPI GetZkDevice(string address)
        {
            if (!zkDeviceCache.ContainsKey(address))
            {
                ZKDeviceAPI zkDeviceApi = new ZKDeviceAPIImpl();
                if (zkDeviceApi.Connect(address, 4370))
                {
                    log.InfoFormat("设备{0}:{1}连接成功", address,4370);
                }
                zkDeviceCache.Add(address, zkDeviceApi);
            }

            return zkDeviceCache[address];
        }

        /// <summary>
        /// 更新下载任务状态
        /// </summary>
        private void UpdateRecord(AccessControlState oldState, AccessControlState newState,long id)
        {
            DatabaseHelper db = new DatabaseHelper();
            string now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string sql_update = string.Format("update AccessControlRecord set accessControlState = {0} ,uploadTime='{1}' where id = {2} and accessControlState = {3}", (int)newState,now,id, (int)oldState);
            db.execute(ip, username, password, sql_update,null);
            log.InfoFormat("更新权限状态从 【{0}】 到 【{1}】", oldState, newState);
        }

        /// <summary>
        /// 删除成功的取消授权任务
        /// </summary>
        private void DeleteSuccessRecord(AccessControlState state, long id)
        {
            DatabaseHelper db = new DatabaseHelper();
            string sql_update = string.Format("delete from AccessControlRecord where id = {0} and accessControlState = {1}", id,(int)state);
            db.execute(ip, username, password, sql_update,null);
            log.InfoFormat("删除权限状态 【{0}】", state);
        }

        private void UpdateDataGridViewMethod(DataSet ds)
        {
            dataGridView_task.DataSource = ds.Tables[0];
            dataGridView_task.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
            this.Hide();
        }
         
        private void timer1_Tick(object sender, EventArgs e)
        {
            if(zkDeviceNameCache.Count == 0)
            {
                string sql_deviceList = "select address,linkName from Link where protocol = 'Attendance'";
                DataSet ds = db.query(ip, username, password, sql_deviceList);
                DataRowCollection rows = ds.Tables[0].Rows;
                foreach (DataRow dr in rows)
                {
                    string address = dr.Field<string>("address").Split(':')[0];
                    string linkName = dr.Field<string>("linkName");
                    zkDeviceNameCache.Add(address, linkName);
                }
            }

            flowLayoutPanel1.Controls.Clear();

            foreach (KeyValuePair<string, string> kvp in zkDeviceNameCache)
            {
                Button button = new Button();
                button.BackgroundImageLayout = ImageLayout.None;

                button.ImageAlign = ContentAlignment.TopCenter;
                button.Size = new Size(100, 140);
                button.TabIndex = 0;
                button.Text = kvp.Value;
                button.TextAlign = ContentAlignment.BottomCenter;
                button.UseVisualStyleBackColor = true;

                if (zkDeviceCache.ContainsKey(kvp.Key))
                {
                    ZKDeviceAPI zkDeviceApi = null;
                    zkDeviceCache.TryGetValue(kvp.Key,out zkDeviceApi);
                    if (zkDeviceApi != null && zkDeviceApi.IsConnected())
                    {
                        button.Image = Properties.Resources.Finger_online;
                    }
                    else
                    {
                        button.Image = Properties.Resources.Finger_offline;
                    }
                }
                else
                {
                    button.Image = Properties.Resources.Finger_offline;
                }
                

                flowLayoutPanel1.Controls.Add(button);
            }
            flowLayoutPanel1.Invalidate();
        }
    }
}
